-- Type package declaration
create or replace package PDTypes  
as
    TYPE ref_cursor IS REF CURSOR;
end;

-- Integrity package declaration
create or replace package IntegrityPackage AS
 procedure InitNestLevel;
 function GetNestLevel return number;
 procedure NextNestLevel;
 procedure PreviousNestLevel;
 end IntegrityPackage;
/

-- Integrity package definition
create or replace package body IntegrityPackage AS
 NestLevel number;

-- Procedure to initialize the trigger nest level
 procedure InitNestLevel is
 begin
 NestLevel := 0;
 end;


-- Function to return the trigger nest level
 function GetNestLevel return number is
 begin
 if NestLevel is null then
     NestLevel := 0;
 end if;
 return(NestLevel);
 end;

-- Procedure to increase the trigger nest level
 procedure NextNestLevel is
 begin
 if NestLevel is null then
     NestLevel := 0;
 end if;
 NestLevel := NestLevel + 1;
 end;

-- Procedure to decrease the trigger nest level
 procedure PreviousNestLevel is
 begin
 NestLevel := NestLevel - 1;
 end;

 end IntegrityPackage;
/


drop trigger TR_ASIGNATURA
/

drop trigger TR_BITACORA
/

drop trigger TR_CARGA
/

drop trigger TR_ESTUDIANTE
/

drop trigger TR_GRUPO
/

drop trigger TR_INSCRIPCION
/

drop trigger TR_PROFESOR
/

alter table ASIGNATURA
   drop constraint FK_ASIGNATU_INS_ASI_INSCRIPC
/

alter table CAR_PRO
   drop constraint FK_CAR_PRO_CAR_PRO2_PROFESOR
/

alter table CAR_PRO
   drop constraint FK_CAR_PRO_CAR_PRO_CAR_ACA
/

alter table GRUPO
   drop constraint FK_GRUPO_ASIGNATUR_ASIGNATU
/

alter table GRUPO
   drop constraint FK_GRUPO_GRUPO_CAR_CAR_ACA
/

alter table GRUPO_ESPACIO
   drop constraint FK_GRUPO_ES_GRUPO_ESP_ESPACIOF
/

alter table GRUPO_ESPACIO
   drop constraint FK_GRUPO_ES_GRUPO_ESP_GRUPO
/

alter table INSCRI_ESTU
   drop constraint FK_INSCRI_E_INSCRI_ES_ESTUDIAN
/

alter table INSCRI_ESTU
   drop constraint FK_INSCRI_E_INSCRI_ES_INSCRIPC
/

drop view V_ASIGNATURA
/

drop view V_BITACORA
/

drop view V_CAR_ACA
/

drop view V_CAR_PRO
/

drop view V_CONTROL_B
/

drop view V_ESPACIOFISICO
/

drop view V_ESTUDIANTE
/

drop view V_GRUPO
/

drop view V_GRUPO_ESPACIO
/

drop view V_INSCRIPCION
/

drop view V_INSCRI_ESTU
/

drop view V_PROFESOR
/

drop index INS_ASI_FK
/

drop table ASIGNATURA cascade constraints
/

drop table BITACORA cascade constraints
/

drop table CAR_ACA cascade constraints
/

drop index CAR_PRO2_FK
/

drop index CAR_PRO_FK
/

drop table CAR_PRO cascade constraints
/

drop table CONTROL_B cascade constraints
/

drop table ESPACIOFISICO cascade constraints
/

drop table ESTUDIANTE cascade constraints
/

drop index GRUPO_CAR_FK
/

drop index ASIGNATURA_GRUPO_FK
/

drop table GRUPO cascade constraints
/

drop index GRUPO_ESPACIO2_FK
/

drop index GRUPO_ESPACIO_FK
/

drop table GRUPO_ESPACIO cascade constraints
/

drop table INSCRIPCION cascade constraints
/

drop index INSCRI_ESTU2_FK
/

drop index INSCRI_ESTU_FK
/

drop table INSCRI_ESTU cascade constraints
/

drop table PROFESOR cascade constraints
/

/*==============================================================*/
/* Table: ASIGNATURA                                            */
/*==============================================================*/
create table ASIGNATURA 
(
   ASI_ID               INTEGER              not null,
   ASI_NOMBRE           VARCHAR2(20)         not null,
   INS_ID               INTEGER,
   ASI_HORA             INTEGER,
   ASI_CREDITOS         INTEGER,
   ASI_TIPO             VARCHAR2(256),
   constraint PK_ASIGNATURA primary key (ASI_ID, ASI_NOMBRE),
   constraint CKC_ASI_HORAS_ASIGNATU check (ASI_HORA BETWEEN 1 AND 4),
   constraint CKC_ASI_CREDITOS_ASIGNATU check (ASI_CREDITOS BETWEEN 1 AND 5)
)
/

/*==============================================================*/
/* Index: INS_ASI_FK                                            */
/*==============================================================*/
create index INS_ASI_FK on ASIGNATURA (
   INS_ID ASC
)
/

/*==============================================================*/
/* Table: BITACORA                                              */
/*==============================================================*/
create table BITACORA 
(
   BIT_ID               INTEGER              not null,
   BIT_TABLA            VARCHAR2(20),
   BIT_OPERACION        VARCHAR2(10),
   BIT_USUARIO          VARCHAR2(30),
   BIT_MAQUINA          VARCHAR2(50),
   BIT_FECHA            DATE,
   BIT_DATOS            VARCHAR2(50),
   constraint PK_BITACORA primary key (BIT_ID)
)
/

/*==============================================================*/
/* Table: CAR_ACA                                               */
/*==============================================================*/
create table CAR_ACA 
(
   CAR_ID               INTEGER              not null,
   CAR_ANO              DATE,
   CAR_SEMES            DATE,
   constraint PK_CAR_ACA primary key (CAR_ID)
)
/

/*==============================================================*/
/* Table: CAR_PRO                                               */
/*==============================================================*/
create table CAR_PRO 
(
   CAR_ID               INTEGER              not null,
   PRO_ID               INTEGER              not null,
   constraint PK_CAR_PRO primary key (CAR_ID, PRO_ID)
)
/

/*==============================================================*/
/* Index: CAR_PRO_FK                                            */
/*==============================================================*/
create index CAR_PRO_FK on CAR_PRO (
   CAR_ID ASC
)
/

/*==============================================================*/
/* Index: CAR_PRO2_FK                                           */
/*==============================================================*/
create index CAR_PRO2_FK on CAR_PRO (
   PRO_ID ASC
)
/

/*==============================================================*/
/* Table: CONTROL_B                                             */
/*==============================================================*/
create table CONTROL_B 
(
   CRTL_ID              INTEGER,
   CRTL_OPERACION       VARCHAR2(10),
   CRTL_USUARIO         VARCHAR2(30),
   CRTL_MAQUINA         VARCHAR2(50),
   CRTL_FECHA           DATE
)
/

/*==============================================================*/
/* Table: ESPACIOFISICO                                         */
/*==============================================================*/
create table ESPACIOFISICO 
(
   ESP_ID               INTEGER              not null,
   ESP_NOMBRE           VARCHAR2(50)         not null,
   constraint PK_ESPACIOFISICO primary key (ESP_ID, ESP_NOMBRE)
)
/

/*==============================================================*/
/* Table: ESTUDIANTE                                            */
/*==============================================================*/
create table ESTUDIANTE 
(
   ESTU_ID              INTEGER              not null,
   ESTU_NOMBRES         VARCHAR2(50),
   ESTU_APELLIDOS       VARCHAR2(50),
   ESTU_TELEFONO        NUMBER(7),
   ESTU_GENERO          VARCHAR2(10),
   ESTU_DIRECCION       VARCHAR2(20),
   ESTU_CELULAR         NUMBER(10),
   ESTU_NACIMIENTO      DATE,
   ESTU_INGRESOU        DATE,
   ESTU_CODIGO          INTEGER              not null,
   ESTU_PROGRAMA        VARCHAR2(20),
   constraint PK_ESTUDIANTE primary key (ESTU_ID, ESTU_CODIGO),
   constraint CKC_EST_NACE_ESTUDIAN check ((ESTU_NACIMIENTO < DATEADD(YEAR, -12, GETDATE()))AND
													   (ESTU_NACIMIENTO < ESTU_INGRESOU)),
   constraint CKC_EST_GENERO_ESTUDIAN check (ESTU_GENERO IN ('M','F')),
   constraint CKC_EST_TELEFONO_ESTUDIAN check (ESTU_TELEFONO LIKE '[5][7][1,2,4,5,6,7,8][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
   constraint CKC_EST_CELULAR_ESTUDIAN check (((SUBSTRING(ESTU_CELULAR, 1, 3) = '300' OR
														      SUBSTRING(ESTU_CELULAR, 1, 3) = '301' OR
														      SUBSTRING(ESTU_CELULAR, 1, 3) = '302' OR
														      SUBSTRING(ESTU_CELULAR, 1, 3) = '310' OR
														      SUBSTRING(ESTU_CELULAR, 1, 3) = '311' OR
														      SUBSTRING(ESTU_CELULAR, 1, 3) = '312' OR
														      SUBSTRING(ESTU_CELULAR, 1, 3) = '315' OR
														      SUBSTRING(ESTU_CELULAR, 1, 3) = '316') AND
														     (CONVERT(INTEGER, SUBSTRING(ESTU_CELULAR, 4, 10)) BETWEEN 0 AND 9999999)))
)
/

/*==============================================================*/
/* Table: GRUPO                                                 */
/*==============================================================*/
create table GRUPO 
(
   GRUPO_ID             INTEGER              not null,
   CAR_ID               INTEGER,
   ASI_ID               INTEGER,
   ASI_NOMBRE           VARCHAR2(20),
   constraint PK_GRUPO primary key (GRUPO_ID)
)
/

/*==============================================================*/
/* Index: ASIGNATURA_GRUPO_FK                                   */
/*==============================================================*/
create index ASIGNATURA_GRUPO_FK on GRUPO (
   ASI_ID ASC,
   ASI_NOMBRE ASC
)
/

/*==============================================================*/
/* Index: GRUPO_CAR_FK                                          */
/*==============================================================*/
create index GRUPO_CAR_FK on GRUPO (
   CAR_ID ASC
)
/

/*==============================================================*/
/* Table: GRUPO_ESPACIO                                         */
/*==============================================================*/
create table GRUPO_ESPACIO 
(
   GRUPO_ID             INTEGER              not null,
   ESP_ID               INTEGER              not null,
   ESP_NOMBRE           VARCHAR2(50)         not null,
   constraint PK_GRUPO_ESPACIO primary key (GRUPO_ID, ESP_ID, ESP_NOMBRE)
)
/

/*==============================================================*/
/* Index: GRUPO_ESPACIO_FK                                      */
/*==============================================================*/
create index GRUPO_ESPACIO_FK on GRUPO_ESPACIO (
   GRUPO_ID ASC
)
/

/*==============================================================*/
/* Index: GRUPO_ESPACIO2_FK                                     */
/*==============================================================*/
create index GRUPO_ESPACIO2_FK on GRUPO_ESPACIO (
   ESP_ID ASC,
   ESP_NOMBRE ASC
)
/

/*==============================================================*/
/* Table: INSCRIPCION                                           */
/*==============================================================*/
create table INSCRIPCION 
(
   INS_ID               INTEGER              not null,
   INS_ANO              DATE,
   INS_SEMES            DATE,
   INS_NOTA             NUMBER(2)           
      constraint CKC_INS_NOTA_INSCRIPC check (INS_NOTA is null or (INS_NOTA between 0 and 50)),
   constraint PK_INSCRIPCION primary key (INS_ID)
)
/

/*==============================================================*/
/* Table: INSCRI_ESTU                                           */
/*==============================================================*/
create table INSCRI_ESTU 
(
   INS_ID               INTEGER              not null,
   ESTU_ID              INTEGER              not null,
   ESTU_CODIGO          INTEGER              not null,
   constraint PK_INSCRI_ESTU primary key (INS_ID, ESTU_ID, ESTU_CODIGO)
)
/

/*==============================================================*/
/* Index: INSCRI_ESTU_FK                                        */
/*==============================================================*/
create index INSCRI_ESTU_FK on INSCRI_ESTU (
   INS_ID ASC
)
/

/*==============================================================*/
/* Index: INSCRI_ESTU2_FK                                       */
/*==============================================================*/
create index INSCRI_ESTU2_FK on INSCRI_ESTU (
   ESTU_ID ASC,
   ESTU_CODIGO ASC
)
/

/*==============================================================*/
/* Table: PROFESOR                                              */
/*==============================================================*/
create table PROFESOR 
(
   PRO_ID               INTEGER              not null,
   PRO_NOMBRES          VARCHAR2(50)         not null,
   PRO_APELLIDOS        VARCHAR2(50)         not null,
   PRO_SUELDO           NUMBER(8,2),
   PRO_TELEFONO         NUMBER(7)            not null,
   PRO_CELULAR          NUMBER(10)           not null,
   PRO_DIRECCION        VARCHAR2(20),
   PRO_NACIMIENTO       DATE                 not null,
   PRO_GENERO           VARCHAR2(10)         not null,
   PRO_GRADO            DATE                 not null,
   PRO_INGRESOU         DATE                 not null,
   constraint PK_PROFESOR primary key (PRO_ID),
   constraint CKC_PRO_NACE_PROFESOR check ((PRO_NACIMIENTO < DATEADD(YEAR, -23, GETDATE()))AND
													   (PRO_NACIMIENTO < PRO_INGRESOU)),
   constraint CKC_PRO_SUELDO_PROFESOR check (PRO_SUELDO BETWEEN 0 AND 5000000),
   constraint CKC_PRO_ENTRA_PROFESOR check (((PRO_INGRESOU > DATEADD(YEAR,4,PRO_FECHA1))AND
													   (PRO_INGRESOU <= GETDATE()))),
   constraint CKC_PRO_GENERO_PROFESOR check (PRO_GENERO IN ('M','F')),
   constraint CKC_PRO_TELEFONO_PROFESOR check (PRO_TELEFONO LIKE '[5][7][1,2,4,5,6,7,8][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
   constraint CKC_PRO_CELULAR_PROFESOR check (((SUBSTRING(PRO_CELULAR, 1, 3) = '300' OR
														      SUBSTRING(PRO_CELULAR, 1, 3) = '301' OR
														      SUBSTRING(PRO_CELULAR, 1, 3) = '302' OR
														      SUBSTRING(PRO_CELULAR, 1, 3) = '310' OR
														      SUBSTRING(PRO_CELULAR, 1, 3) = '311' OR
														      SUBSTRING(PRO_CELULAR, 1, 3) = '312' OR
														      SUBSTRING(PRO_CELULAR, 1, 3) = '315' OR
														      SUBSTRING(PRO_CELULAR, 1, 3) = '316') AND
														     (CONVERT(INTEGER, SUBSTRING(PRO_CELULAR, 4, 10)) BETWEEN 0 AND 9999999)))
)
/

/*==============================================================*/
/* View: V_ASIGNATURA                                           */
/*==============================================================*/
create or replace view V_ASIGNATURA as
SELECT
   ASIGNATURA.ASI_ID,
   ASIGNATURA.ASI_NOMBRE,
   ASIGNATURA.INS_ID,
   ASIGNATURA.ASI_HORA,
   ASIGNATURA.ASI_CREDITOS,
   ASIGNATURA.ASI_TIPO
FROM
   ASIGNATURA
with read only
/

/*==============================================================*/
/* View: V_BITACORA                                             */
/*==============================================================*/
create or replace view V_BITACORA as
SELECT
   BITACORA.BIT_ID,
  BITACORA.BIT_TABLA,
   BITACORA.BIT_OPERACION,
   BITACORA.BIT_USUARIO,
   BITACORA.BIT_MAQUINA,
   BITACORA.BIT_FECHA,
   BITACORA.BIT_DATOS
FROM
   BITACORA
with read only
/

/*==============================================================*/
/* View: V_CAR_ACA                                              */
/*==============================================================*/
create or replace view V_CAR_ACA as
SELECT
   CAR_ACA.CAR_ID,
   CAR_ACA.CAR_ANO,
   CAR_ACA.CAR_SEMES
FROM
   CAR_ACA
with read only
/

/*==============================================================*/
/* View: V_CAR_PRO                                              */
/*==============================================================*/
create or replace view V_CAR_PRO as
select
    CAR_PRO.CAR_ID,
    CAR_PRO.PRO_ID
from
    CAR_PRO
with read only
/

/*==============================================================*/
/* View: V_CONTROL_B                                            */
/*==============================================================*/
create or replace view V_CONTROL_B as
SELECT
   CONTROL_B.CRTL_ID,
  CONTROL_B.CRTL_OPERACION,
   CONTROL_B.CRTL_USUARIO,
   CONTROL_B.CRTL_MAQUINA,
   CONTROL_B.CRTL_FECHA,
   CONTROL_B.CRTL_DATOS
FROM
   CONTROL_B
with read only
/

/*==============================================================*/
/* View: V_ESPACIOFISICO                                        */
/*==============================================================*/
create or replace view V_ESPACIOFISICO as
select
    ESPACIOFISICO.ESP_ID,
    ESPACIOFISICO.ESP_NOMBRE
    
from
    ESPACIOFISICO
with read only
/

/*==============================================================*/
/* View: V_ESTUDIANTE                                           */
/*==============================================================*/
create or replace view V_ESTUDIANTE as
SELECT
   ESTUDIANTE.ESTU_ID,
   ESTUDIANTE.ESTU_NOMBRES,
   ESTUDIANTE.ESTU_APELLIDOS,
   ESTUDIANTE.ESTU_TELEFONO,
   ESTUDIANTE.ESTU_GENERO,
   ESTUDIANTE.ESTU_DIRECCION,
   ESTUDIANTE.ESTU_CELULAR,
   ESTUDIANTE.ESTU_NACIMIENTO,
   ESTUDIANTE.ESTU_INGRESOU,
   ESTUDIANTE.ESTU_CODIGO,
   ESTUDIANTE.ESTU_PROGRAMA
  
FROM
   ESTUDIANTE
with read only
/

/*==============================================================*/
/* View: V_GRUPO                                                */
/*==============================================================*/
create or replace view V_GRUPO as
SELECT
   GRUPO.GRUPO_ID,
   GRUPO.CAR_ID,
   GRUPO.ASI_ID,
   GRUPO.ASI_NOMBRE
FROM
   GRUPO
with read only
/

/*==============================================================*/
/* View: V_GRUPO_ESPACIO                                        */
/*==============================================================*/
create or replace view V_GRUPO_ESPACIO as
select
    GRUPO_ESPACIO.GRUPO_ID,
    GRUPO_ESPACIO.ESP_ID,
    GRUPO_ESPACIO.ESP_NOMBRE
from
    GRUPO_ESPACIO
with read only
/

/*==============================================================*/
/* View: V_INSCRIPCION                                          */
/*==============================================================*/
create or replace view V_INSCRIPCION as
select
    INSCRIPCION.INS_ID,
    INSCRIPCION.INS_ANO,
    INSCRIPCION.INS_SEMES,
    INSCRIPCION.INS_NOTA
from
    INSCRIPCION
with read only
/

/*==============================================================*/
/* View: V_INSCRI_ESTU                                          */
/*==============================================================*/
create or replace view V_INSCRI_ESTU as
select
    INSCRI_ESTU.INS_ID,
    INSCRI_ESTU.ESTU_ID,
    INSCRI_ESTU.ESTU_CODIGO
from
    INSCRI_ESTU
with read only
/

/*==============================================================*/
/* View: V_PROFESOR                                             */
/*==============================================================*/
create or replace view V_PROFESOR as
SELECT
   PROFESOR.PRO_ID,
   PROFESOR.PRO_NOMBRES,
   PROFESOR.PRO_APELLIDOS,
   PROFESOR.PRO_SUELDO,
   PROFESOR.PRO_TELEFONO,
   PROFESOR.PRO_CELULAR,
   PROFESOR.PRO_DIRECCION,
   PROFESOR.PRO_NACIMIENTO,
   PROFESOR.PRO_GENERO,
   PROFESOR.PRO_GRADO,
   PROFESOR.PRO_INGRESOU
   
FROM
   PROFESOR
with read only
/

alter table ASIGNATURA
   add constraint FK_ASIGNATU_INS_ASI_INSCRIPC foreign key (INS_ID)
      references INSCRIPCION (INS_ID)
/

alter table CAR_PRO
   add constraint FK_CAR_PRO_CAR_PRO2_PROFESOR foreign key (PRO_ID)
      references PROFESOR (PRO_ID)
/

alter table CAR_PRO
   add constraint FK_CAR_PRO_CAR_PRO_CAR_ACA foreign key (CAR_ID)
      references CAR_ACA (CAR_ID)
/

alter table GRUPO
   add constraint FK_GRUPO_ASIGNATUR_ASIGNATU foreign key (ASI_ID, ASI_NOMBRE)
      references ASIGNATURA (ASI_ID, ASI_NOMBRE)
/

alter table GRUPO
   add constraint FK_GRUPO_GRUPO_CAR_CAR_ACA foreign key (CAR_ID)
      references CAR_ACA (CAR_ID)
/

alter table GRUPO_ESPACIO
   add constraint FK_GRUPO_ES_GRUPO_ESP_ESPACIOF foreign key (ESP_ID, ESP_NOMBRE)
      references ESPACIOFISICO (ESP_ID, ESP_NOMBRE)
/

alter table GRUPO_ESPACIO
   add constraint FK_GRUPO_ES_GRUPO_ESP_GRUPO foreign key (GRUPO_ID)
      references GRUPO (GRUPO_ID)
/

alter table INSCRI_ESTU
   add constraint FK_INSCRI_E_INSCRI_ES_ESTUDIAN foreign key (ESTU_ID, ESTU_CODIGO)
      references ESTUDIANTE (ESTU_ID, ESTU_CODIGO)
/

alter table INSCRI_ESTU
   add constraint FK_INSCRI_E_INSCRI_ES_INSCRIPC foreign key (INS_ID)
      references INSCRIPCION (INS_ID)
/


create trigger TR_ASIGNATURA on ASIGNATURA before insert, update, delete as
--DETERMINE IF THIS IS AN INSERT,UPDATE, OR DELETE ACTION OR A "FAILED DELETE".
    DECLARE @ACTION CHAR(1)
    DECLARE @THIS_TABLE VARCHAR(20)
    DECLARE @USER_MACHINE VARCHAR(255)
    SELECT @THIS_TABLE = 'ASIGNATURA'
    SELECT @USER_MACHINE = HOST_NAME()
    
    SET @ACTION = (CASE WHEN EXISTS(SELECT * FROM inserted)
                         AND EXISTS(SELECT * FROM deleted)
                            THEN 'U'  -- SET ACTION TO UPDATED.
                        WHEN EXISTS(SELECT * FROM inserted)
                            THEN 'I'  -- SET ACTION TO INSERT.
                        WHEN EXISTS(SELECT * FROM deleted)
                            THEN 'D'  -- SET ACTION TO deleted.
                        ELSE NULL -- SKIP. IT MAY HAVE BEEN A "FAILED DELETE".   
                    END)
                    
     IF (@ACTION = 'I')
     BEGIN
        DECLARE @VARID VARCHAR(12)
	    SELECT @VARID = (SELECT ASI_ID FROM inserted)
        INSERT INTO BITACORA VALUES(@THIS_TABLE,@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID)
     END
     
     IF (@ACTION = 'U')
     BEGIN
        DECLARE C CURSOR  FOR
        SELECT ASI_ID
        FROM inserted

    	DECLARE @VARID1 VARCHAR(12)

    	-- OPEN THE CURSOR
    	OPEN C

    	FETCH NEXT FROM C INTO @VARID1
    	WHILE (@@FETCH_STATUS = 0)
    	BEGIN
    		 INSERT INTO BITACORA VALUES(@THIS_TABLE,@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID1)

    		-- DO OTHER STUFF

    		FETCH NEXT FROM C INTO @VARID1
    	END

    	-- CLOSE AND DEALLOCATE THE CURSOR
    	CLOSE C
    	DEALLOCATE C
     END
     
     
     IF (@ACTION = 'D')
     BEGIN
        DECLARE C CURSOR  FOR
        SELECT ASI_ID
        FROM deleted

    	DECLARE @VARID2 VARCHAR(12)

    	-- OPEN THE CURSOR
    	OPEN C

    	FETCH NEXT FROM C INTO @VARID2
    	WHILE (@@FETCH_STATUS = 0)
    	BEGIN
    		 INSERT INTO BITACORA VALUES(@THIS_TABLE,@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID2)

    		-- DO OTHER STUFF

    		FETCH NEXT FROM C INTO @VARID2
    	END

    	-- CLOSE AND DEALLOCATE THE CURSOR
    	CLOSE C
    	DEALLOCATE C
     END
/


create trigger TR_BITACORA on BITACORA before insert, update, delete as
--DETERMINE IF THIS IS AN INSERT,UPDATE, OR DELETE ACTION OR A "FAILED DELETE".
    DECLARE @ACTION CHAR(1)
    DECLARE @THIS_TABLE VARCHAR(20)
    DECLARE @USER_MACHINE VARCHAR(255)
    SELECT @THIS_TABLE = 'CARGA'
    SELECT @USER_MACHINE = HOST_NAME()
    
    SET @ACTION = (CASE WHEN EXISTS(SELECT * FROM inserted)
                         AND EXISTS(SELECT * FROM deleted)
                            THEN 'U'  -- SET ACTION TO UPDATED.
                        WHEN EXISTS(SELECT * FROM inserted)
                            THEN 'I'  -- SET ACTION TO INSERT.
                        WHEN EXISTS(SELECT * FROM deleted)
                            THEN 'D'  -- SET ACTION TO deleted.
                        ELSE NULL -- SKIP. IT MAY HAVE BEEN A "FAILED DELETE".   
                    END)
                    
     IF (@ACTION = 'I')
     BEGIN
        DECLARE @VARID INT
	    SELECT @VARID = (SELECT BIT_ID FROM inserted)
         
        INSERT INTO CONTROLB VALUES(@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID)
     END
     
     IF (@ACTION = 'U')
     BEGIN
        DECLARE C CURSOR  FOR
        SELECT BIT_ID
        FROM inserted

    	DECLARE @VARID1 INT

    	-- OPEN THE CURSOR
    	OPEN C

    	FETCH NEXT FROM C INTO @VARID1
    	WHILE (@@FETCH_STATUS = 0)
    	BEGIN
    		 INSERT INTO CONTROLB VALUES(@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID1)
    		-- DO OTHER STUFF

    		FETCH NEXT FROM C INTO @VARID1
    	END

    	-- CLOSE AND DEALLOCATE THE CURSOR
    	CLOSE C
    	DEALLOCATE C
     END
     
     
     IF (@ACTION = 'D')
     BEGIN
        DECLARE C CURSOR  FOR
        SELECT BIT_ID
        FROM deleted

    	DECLARE @VARID2 INT

    	-- OPEN THE CURSOR
    	OPEN C

    	FETCH NEXT FROM C INTO @VARID2
    	WHILE (@@FETCH_STATUS = 0)
    	BEGIN
    		 INSERT INTO CONTROLB VALUES(@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID2)
    		-- DO OTHER STUFF

    		FETCH NEXT FROM C INTO @VARID2
    	END

    	-- CLOSE AND DEALLOCATE THE CURSOR
    	CLOSE C
    	DEALLOCATE C
     END
/


create trigger TR_CARGA on CAR_ACA before insert, update, delete as
--DETERMINE IF THIS IS AN INSERT,UPDATE, OR DELETE ACTION OR A "FAILED DELETE".
    DECLARE @ACTION CHAR(1)
    DECLARE @THIS_TABLE VARCHAR(20)
    DECLARE @USER_MACHINE VARCHAR(255)
    SELECT @THIS_TABLE = 'CARGA'
    SELECT @USER_MACHINE = HOST_NAME()
    
    SET @ACTION = (CASE WHEN EXISTS(SELECT * FROM inserted)
                         AND EXISTS(SELECT * FROM deleted)
                            THEN 'U'  -- SET ACTION TO UPDATED.
                        WHEN EXISTS(SELECT * FROM inserted)
                            THEN 'I'  -- SET ACTION TO INSERT.
                        WHEN EXISTS(SELECT * FROM deleted)
                            THEN 'D'  -- SET ACTION TO deleted.
                        ELSE NULL -- SKIP. IT MAY HAVE BEEN A "FAILED DELETE".   
                    END)
                    
     IF (@ACTION = 'I')
     BEGIN
        DECLARE @VARID VARCHAR(12)
	    SELECT @VARID = (SELECT CAR_ID FROM inserted)
        INSERT INTO BITACORA VALUES(@THIS_TABLE,@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID)
     END
     
     IF (@ACTION = 'U')
     BEGIN
        DECLARE C CURSOR  FOR
        SELECT CAR_ID
        FROM inserted

    	DECLARE @VARID1 VARCHAR(12)

    	-- OPEN THE CURSOR
    	OPEN C

    	FETCH NEXT FROM C INTO @VARID1
    	WHILE (@@FETCH_STATUS = 0)
    	BEGIN
    		 INSERT INTO BITACORA VALUES(@THIS_TABLE,@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID1)

    		-- DO OTHER STUFF

    		FETCH NEXT FROM C INTO @VARID1
    	END

    	-- CLOSE AND DEALLOCATE THE CURSOR
    	CLOSE C
    	DEALLOCATE C
     END
     
     
     IF (@ACTION = 'D')
     BEGIN
        DECLARE C CURSOR  FOR
        SELECT CAR_ID
        FROM deleted

    	DECLARE @VARID2 VARCHAR(12)

    	-- OPEN THE CURSOR
    	OPEN C

    	FETCH NEXT FROM C INTO @VARID2
    	WHILE (@@FETCH_STATUS = 0)
    	BEGIN
    		 INSERT INTO BITACORA VALUES(@THIS_TABLE,@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID2)

    		-- DO OTHER STUFF

    		FETCH NEXT FROM C INTO @VARID2
    	END

    	-- CLOSE AND DEALLOCATE THE CURSOR
    	CLOSE C
    	DEALLOCATE C
     END
/


create trigger TR_ESTUDIANTE on ESTUDIANTE before insert, update, delete as
--DETERMINE IF THIS IS AN INSERT,UPDATE, OR DELETE ACTION OR A "FAILED DELETE".
    DECLARE @ACTION CHAR(1)
    DECLARE @THIS_TABLE VARCHAR(20)
    DECLARE @USER_MACHINE VARCHAR(255)
    SELECT @THIS_TABLE = 'ESTUDIANTE'
    SELECT @USER_MACHINE = HOST_NAME()
    
    SET @ACTION = (CASE WHEN EXISTS(SELECT * FROM inserted)
                         AND EXISTS(SELECT * FROM deleted)
                            THEN 'U'  -- SET ACTION TO UPDATED.
                        WHEN EXISTS(SELECT * FROM inserted)
                            THEN 'I'  -- SET ACTION TO INSERT.
                        WHEN EXISTS(SELECT * FROM deleted)
                            THEN 'D'  -- SET ACTION TO deleted.
                        ELSE NULL -- SKIP. IT MAY HAVE BEEN A "FAILED DELETE".   
                    END)
                    
     IF (@ACTION = 'I')
     BEGIN
        DECLARE @VARID VARCHAR(12)
	    SELECT @VARID = (SELECT ESTU_ID FROM inserted)
        INSERT INTO BITACORA VALUES(@THIS_TABLE,@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID)
     END
     
     IF (@ACTION = 'U')
     BEGIN
        DECLARE C CURSOR  FOR
        SELECT ESTU_ID
        FROM inserted

    	DECLARE @VARID1 VARCHAR(12)

    	-- OPEN THE CURSOR
    	OPEN C

    	FETCH NEXT FROM C INTO @VARID1
    	WHILE (@@FETCH_STATUS = 0)
    	BEGIN
    		 INSERT INTO BITACORA VALUES(@THIS_TABLE,@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID1)

    		-- DO OTHER STUFF

    		FETCH NEXT FROM C INTO @VARID1
    	END

    	-- CLOSE AND DEALLOCATE THE CURSOR
    	CLOSE C
    	DEALLOCATE C
     END
     
     
     IF (@ACTION = 'D')
     BEGIN
        DECLARE C CURSOR  FOR
        SELECT ESTU_ID
        FROM deleted

    	DECLARE @VARID2 VARCHAR(12)

    	-- OPEN THE CURSOR
    	OPEN C

    	FETCH NEXT FROM C INTO @VARID2
    	WHILE (@@FETCH_STATUS = 0)
    	BEGIN
    		 INSERT INTO BITACORA VALUES(@THIS_TABLE,@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID2)

    		-- DO OTHER STUFF

    		FETCH NEXT FROM C INTO @VARID2
    	END

    	-- CLOSE AND DEALLOCATE THE CURSOR
    	CLOSE C
    	DEALLOCATE C
     END
/


create trigger TR_GRUPO on GRUPO before insert, update, delete as
--DETERMINE IF THIS IS AN INSERT,UPDATE, OR DELETE ACTION OR A "FAILED DELETE".
    DECLARE @ACTION CHAR(1)
    DECLARE @THIS_TABLE VARCHAR(20)
    DECLARE @USER_MACHINE VARCHAR(255)
    SELECT @THIS_TABLE = 'GRUPO'
    SELECT @USER_MACHINE = HOST_NAME()
    
    SET @ACTION = (CASE WHEN EXISTS(SELECT * FROM inserted)
                         AND EXISTS(SELECT * FROM deleted)
                            THEN 'U'  -- SET ACTION TO UPDATED.
                        WHEN EXISTS(SELECT * FROM inserted)
                            THEN 'I'  -- SET ACTION TO INSERT.
                        WHEN EXISTS(SELECT * FROM deleted)
                            THEN 'D'  -- SET ACTION TO deleted.
                        ELSE NULL -- SKIP. IT MAY HAVE BEEN A "FAILED DELETE".   
                    END)
                    
     IF (@ACTION = 'I')
     BEGIN
        DECLARE @VARID VARCHAR(12)
	    SELECT @VARID = (SELECT GRUPO_ID FROM inserted)
        INSERT INTO BITACORA VALUES(@THIS_TABLE,@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID)
     END
     
     IF (@ACTION = 'U')
     BEGIN
        DECLARE C CURSOR  FOR
        SELECT GRUPO_ID
        FROM inserted

    	DECLARE @VARID1 VARCHAR(12)

    	-- OPEN THE CURSOR
    	OPEN C

    	FETCH NEXT FROM C INTO @VARID1
    	WHILE (@@FETCH_STATUS = 0)
    	BEGIN
    		 INSERT INTO BITACORA VALUES(@THIS_TABLE,@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID1)

    		-- DO OTHER STUFF

    		FETCH NEXT FROM C INTO @VARID1
    	END

    	-- CLOSE AND DEALLOCATE THE CURSOR
    	CLOSE C
    	DEALLOCATE C
     END
     
     
     IF (@ACTION = 'D')
     BEGIN
        DECLARE C CURSOR  FOR
        SELECT GRUPO_ID
        FROM deleted

    	DECLARE @VARID2 VARCHAR(12)

    	-- OPEN THE CURSOR
    	OPEN C

    	FETCH NEXT FROM C INTO @VARID2
    	WHILE (@@FETCH_STATUS = 0)
    	BEGIN
    		 INSERT INTO BITACORA VALUES(@THIS_TABLE,@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID2)

    		-- DO OTHER STUFF

    		FETCH NEXT FROM C INTO @VARID2
    	END

    	-- CLOSE AND DEALLOCATE THE CURSOR
    	CLOSE C
    	DEALLOCATE C
     END
/


create trigger TR_INSCRIPCION on INSCRIPCION before insert, update, delete as
--DETERMINE IF THIS IS AN INSERT,UPDATE, OR DELETE ACTION OR A "FAILED DELETE".
    DECLARE @ACTION CHAR(1)
    DECLARE @THIS_TABLE VARCHAR(20)
    DECLARE @USER_MACHINE VARCHAR(255)
    SELECT @THIS_TABLE = 'INSCRIPCION'
    SELECT @USER_MACHINE = HOST_NAME()
    
    SET @ACTION = (CASE WHEN EXISTS(SELECT * FROM inserted)
                         AND EXISTS(SELECT * FROM deleted)
                            THEN 'U'  -- SET ACTION TO UPDATED.
                        WHEN EXISTS(SELECT * FROM inserted)
                            THEN 'I'  -- SET ACTION TO INSERT.
                        WHEN EXISTS(SELECT * FROM deleted)
                            THEN 'D'  -- SET ACTION TO deleted.
                        ELSE NULL -- SKIP. IT MAY HAVE BEEN A "FAILED DELETE".   
                    END)
                    
     IF (@ACTION = 'I')
     BEGIN
        DECLARE @VARID VARCHAR(12)
	    SELECT @VARID = (SELECT INS_ID FROM inserted)
        INSERT INTO BITACORA VALUES(@THIS_TABLE,@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID)
     END
     
     IF (@ACTION = 'U')
     BEGIN
        DECLARE C CURSOR  FOR
        SELECT INS_ID
        FROM inserted

    	DECLARE @VARID1 VARCHAR(12)

    	-- OPEN THE CURSOR
    	OPEN C

    	FETCH NEXT FROM C INTO @VARID1
    	WHILE (@@FETCH_STATUS = 0)
    	BEGIN
    		 INSERT INTO BITACORA VALUES(@THIS_TABLE,@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID1)

    		-- DO OTHER STUFF

    		FETCH NEXT FROM C INTO @VARID1
    	END

    	-- CLOSE AND DEALLOCATE THE CURSOR
    	CLOSE C
    	DEALLOCATE C
     END
     
     
     IF (@ACTION = 'D')
     BEGIN
        DECLARE C CURSOR  FOR
        SELECT INS_ID
        FROM deleted

    	DECLARE @VARID2 VARCHAR(12)

    	-- OPEN THE CURSOR
    	OPEN C

    	FETCH NEXT FROM C INTO @VARID2
    	WHILE (@@FETCH_STATUS = 0)
    	BEGIN
    		 INSERT INTO BITACORA VALUES(@THIS_TABLE,@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID2)

    		-- DO OTHER STUFF

    		FETCH NEXT FROM C INTO @VARID2
    	END

    	-- CLOSE AND DEALLOCATE THE CURSOR
    	CLOSE C
    	DEALLOCATE C
     END
/


create trigger TR_PROFESOR on PROFESOR before insert, update, delete as
--DETERMINE IF THIS IS AN INSERT,UPDATE, OR DELETE ACTION OR A "FAILED DELETE".
    DECLARE @ACTION CHAR(1)
    DECLARE @THIS_TABLE VARCHAR(20)
    DECLARE @USER_MACHINE VARCHAR(255)
    SELECT @THIS_TABLE = 'PROFESOR'
    SELECT @USER_MACHINE = HOST_NAME()
    
    SET @ACTION = (CASE WHEN EXISTS(SELECT * FROM inserted)
                         AND EXISTS(SELECT * FROM deleted)
                            THEN 'U'  -- SET ACTION TO UPDATED.
                        WHEN EXISTS(SELECT * FROM inserted)
                            THEN 'I'  -- SET ACTION TO INSERT.
                        WHEN EXISTS(SELECT * FROM deleted)
                            THEN 'D'  -- SET ACTION TO deleted.
                        ELSE NULL -- SKIP. IT MAY HAVE BEEN A "FAILED DELETE".   
                    END)
                    
     IF (@ACTION = 'I')
     BEGIN
        DECLARE @VARID VARCHAR(12)
	    SELECT @VARID = (SELECT PRO_ID FROM inserted)
        INSERT INTO BITACORA VALUES(@THIS_TABLE,@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID)
     END
     
     IF (@ACTION = 'U')
     BEGIN
        DECLARE C CURSOR  FOR
        SELECT PRO_ID
        FROM inserted

    	DECLARE @VARID1 VARCHAR(12)

    	-- OPEN THE CURSOR
    	OPEN C

    	FETCH NEXT FROM C INTO @VARID1
    	WHILE (@@FETCH_STATUS = 0)
    	BEGIN
    		 INSERT INTO BITACORA VALUES(@THIS_TABLE,@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID1)

    		-- DO OTHER STUFF

    		FETCH NEXT FROM C INTO @VARID1
    	END

    	-- CLOSE AND DEALLOCATE THE CURSOR
    	CLOSE C
    	DEALLOCATE C
     END
     
     
     IF (@ACTION = 'D')
     BEGIN
        DECLARE C CURSOR  FOR
        SELECT PRO_ID
        FROM deleted

    	DECLARE @VARID2 VARCHAR(12)

    	-- OPEN THE CURSOR
    	OPEN C

    	FETCH NEXT FROM C INTO @VARID2
    	WHILE (@@FETCH_STATUS = 0)
    	BEGIN
    		 INSERT INTO BITACORA VALUES(@THIS_TABLE,@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID2)

    		-- DO OTHER STUFF

    		FETCH NEXT FROM C INTO @VARID2
    	END

    	-- CLOSE AND DEALLOCATE THE CURSOR
    	CLOSE C
    	DEALLOCATE C
     END
/
